#!/usr/bin/env python
# -*- coding: utf-8 -*-
from utils import log
from sqlalchemy.orm import mapper, sessionmaker
from datetime import datetime
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, BigInteger, String, Unicode, UnicodeText, DateTime
from controls.data_pool_mysql import engine

# 创建到数据库的连接,echo=True表示用logging输出调试结果
metadata = MetaData()
Session = sessionmaker()  # 创建了一个自定义了的 Session类
Session.configure(bind=engine)  # 将创建的数据库连接关联到这个session

"""shout collection has
    userid:     用户id (int)
    content1:     内容 (str)
    content2:     内容2（str）
    content3:     内容3（str）
    ctime:      创建时间(datetime)
"""


class ShoutItem(object):  # 创建一个映射类

    def to_dict(self):
        res = {}
        res['userid'] = self.userid
        res['content1'] = self.content1
        res['content2'] = self.content2
        res['content3'] = self.content3
        if self.ctime:
            res['ctime'] = self.ctime.strftime("%Y-%m-%d %H:%M:%S")
        return res

    def __repr__(self):
        return "%d" % (self.userid)


shout_table = Table(
    'shout', metadata,
    Column('id', Integer, primary_key=True),
    Column('userid', Integer, index=True, unique=True, nullable=False),
    Column('content1', Unicode(191), nullable=True),
    Column('content2', Unicode(191), nullable=True),
    Column('content3', Unicode(191), nullable=True),
    Column('ctime', DateTime, default=datetime.now, nullable=False),
)

mapper(ShoutItem, shout_table)  # 把表映射到类


def create_shoute_table_if_not_exist():
    metadata.create_all(engine)  # 在数据库中生成表


def add_shout_item(p_item):
    item = ShoutItem()
    item.userid = p_item['userid']
    item.content1 = p_item['content1']
    item.content2 = p_item['content2']
    item.content3 = p_item['content3']
    try:
        session = Session()
        session.add(item)  # 往session中添加内容
        session.flush()  # 保存数据
        session.commit()  # 数据库事务的提交,sisson自动过期而不需要关闭
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def get_shout_by_userid(p_userid):
    try:
        session = Session()
        item = session.query(ShoutItem)\
            .filter_by(userid=p_userid).first()
        if item:
            return item.to_dict()
        return None
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def update_shout_by_userid(p_userid, p_item):
    try:
        session = Session()
        session.query(ShoutItem)\
            .filter_by(userid=p_userid).update(p_item)
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


if __name__ == "__main__":
    create_shoute_table_if_not_exist()
